namespace TSL.DAL
{
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using TSL.Model;

    public class SYS_users
    {
        public void Add(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_SYS_users_ADD");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "userpass", DbType.AnsiString, model.userpass);
            db.AddInParameter(dbCommand, "limit", DbType.AnsiString, model.limit);
            db.AddInParameter(dbCommand, "fdnum", DbType.AnsiString, model.fdnum);
            db.AddInParameter(dbCommand, "CHS_name", DbType.AnsiString, model.CHS_name);
            db.AddInParameter(dbCommand, "qq", DbType.AnsiString, model.qq);
            db.AddInParameter(dbCommand, "bank_bank", DbType.AnsiString, model.bank_bank);
            db.AddInParameter(dbCommand, "bank_num", DbType.AnsiString, model.bank_num);
            db.AddInParameter(dbCommand, "userclass", DbType.AnsiString, model.userclass);
            db.AddInParameter(dbCommand, "regfrom", DbType.AnsiString, model.regfrom);
            db.AddInParameter(dbCommand, "rate_dw", DbType.AnsiString, model.rate_dw);
            db.ExecuteNonQuery(dbCommand);
        }

        public void AddMoney(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_AddMoney");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "uermoney", DbType.Decimal, model.uermoney);
            db.AddInParameter(dbCommand, "follows", DbType.AnsiString, model.regfrom);
            db.ExecuteNonQuery(dbCommand);
        }

        public TSL.Model.SYS_users BankNum_ReaderBind(IDataReader dataReader)
        {
            TSL.Model.SYS_users model = new TSL.Model.SYS_users();
            model.username = dataReader["username"].ToString();
            model.BankNum = dataReader["BankNum"].ToString();
            return model;
        }

        public void ChangeFD(string username, int fdnum)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Users_ChangeFD");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, username);
            db.AddInParameter(dbCommand, "fdnum", DbType.AnsiString, fdnum);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Clean_User(string id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Clean_User");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, id);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Delete(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_SYS_users_Delete");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            db.ExecuteNonQuery(dbCommand);
        }

        public void DeleteUser(string ID)
        {
            List<string> strList = new List<string>();
            strList.Add("delete from users where ID = '" + ID + "'");
            DbHelperSQL.ExecuteSqlTran(strList);
        }

        public bool Exists(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from users");
            strSql.Append(" where username=@name ");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@name", SqlDbType.VarChar, 50) };
            parameters[0].Value = name;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public TSL.Model.SYS_users Get_Users_BankNum(string name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Users_BankNum");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, name);
            TSL.Model.SYS_users model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.BankNum_ReaderBind(dataReader);
                }
            }
            return model;
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,name,password,loginNum,upLoginTime,newLoginTime,userTypeID ");
            strSql.Append(" FROM SYS_users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public List<TSL.Model.SYS_users> GetListArrayPage(string strWhere, int PageNo, int PageSize, out int recordCount)
        {
            StringBuilder strSelectSql = new StringBuilder();
            strSelectSql.Append("SELECT * FROM (");
            strSelectSql.Append("SELECT ROW_NUMBER() OVER (ORDER BY ID)");
            strSelectSql.Append("AS Row,* FROM V_sys_users");

            StringBuilder strCoutSql = new StringBuilder();
            strCoutSql.Append("SELECT COUNT(*) as recordCount FROM V_sys_users");

            if (strWhere.Trim() != "")
            {
                strSelectSql.Append(" WHERE " + strWhere);
                strCoutSql.Append(" WHERE " + strWhere);
            }

            strSelectSql.Append(string.Format(")AS TEMP WHERE ROW BETWEEN {0} and {1}", (PageNo * PageSize) - PageSize + 1, PageNo * PageSize));

            List<TSL.Model.SYS_users> list = new List<TSL.Model.SYS_users>();
            recordCount = 0;

            DataSet set = DBUtility.DbHelperSQL.Query(strSelectSql.ToString() + strCoutSql.ToString());

            if (set.Tables.Count == 2)
            {
                recordCount = int.Parse(set.Tables[1].Rows[0]["recordCount"].ToString());
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    list.Add(this.ReaderBind(row));
                }
            }
            return list;
        }

        public List<TSL.Model.SYS_users> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM V_sys_users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            List<TSL.Model.SYS_users> list = new List<TSL.Model.SYS_users>();
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }



        public List<TSL.Model.SYS_users> GetListArray(string strWhere, string username)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM V_sys_users where 1=1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@username", SqlDbType.VarChar, 50) };
            parameters[0].Value = username;

            if (!string.IsNullOrEmpty(username))
            {
                strSql.Append(" and username like  '%'+ @username + '%'  and userclass<>3003 ");
            }
            else
            {
                parameters = null;
            }

            strSql.Append(" order by id desc");
            
            List<TSL.Model.SYS_users> list = new List<TSL.Model.SYS_users>();
            using (IDataReader dataReader = TSL.DBUtility.DbHelperSQL.ExecuteReader(strSql.ToString(), parameters))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public DataSet GetListFromView(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_sys_users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public TSL.Model.SYS_users GetModel(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_GetModelByID");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            TSL.Model.SYS_users model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.SYS_users GetModel(string name, string password)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_GetModel");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, name);
            db.AddInParameter(dbCommand, "password", DbType.AnsiString, password);
            TSL.Model.SYS_users model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.SYS_users GetModelByName(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Users_GetModelByName");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, username);
            TSL.Model.SYS_users model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public DataSet GetUserBank(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_UserBank");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public void online(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update users set nowlogtime=getdate() ");
            strSql.Append(" where username=@name ");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@name", SqlDbType.VarChar, 50) };
            parameters[0].Value = name;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public void QTAdd(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_SYS_users_QTADD");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "userpass", DbType.AnsiString, model.userpass);
            db.AddInParameter(dbCommand, "userclass", DbType.AnsiString, model.userclass);
            db.AddInParameter(dbCommand, "regfrom", DbType.AnsiString, model.regfrom);
            db.AddInParameter(dbCommand, "rate_dw", DbType.AnsiString, model.rate_dw);
            db.AddInParameter(dbCommand, "rate_bdw", DbType.AnsiString, model.rate_bdw);
            db.ExecuteNonQuery(dbCommand);
        }

        public void QTRegister(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_SYS_users_QTRegister");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "userpass", DbType.AnsiString, model.userpass);
            db.AddInParameter(dbCommand, "userclass", DbType.AnsiString, model.userclass);
            db.AddInParameter(dbCommand, "regfrom", DbType.AnsiString, model.regfrom);
            db.AddInParameter(dbCommand, "rate_dw", DbType.AnsiString, model.rate_dw);
            db.AddInParameter(dbCommand, "rate_bdw", DbType.AnsiString, model.rate_bdw);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Rate(string strSql)
        {
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public TSL.Model.SYS_users ReaderBind(DataRow dataRow)
        {
            TSL.Model.SYS_users model = new TSL.Model.SYS_users();
            model.id = dataRow["ID"].ToString();
            model.username = dataRow["username"].ToString();
            model.userpass = dataRow["userpass"].ToString();
            model.CHS_name = dataRow["CHS_name"].ToString();
            model.limit = dataRow["limit"].ToString();
            model.uermoney = Convert.ToDecimal(dataRow["uermoney"]);
            model.lockmoney = Convert.ToDecimal(dataRow["lockmoney"]);
            model.bank_bank = Convert.ToInt32(dataRow["bank_bank"].ToString());
            model.bank_area = dataRow["bank_area"].ToString();
            model.bank_name = dataRow["bank_name"].ToString();
            model.bank_num = dataRow["bank_num"].ToString();
            model.qq = dataRow["qq"].ToString();
            model.addtime = dataRow["addtime"].ToString();
            model.regfrom = dataRow["regfrom"].ToString().Trim();
            model.email = dataRow["email"].ToString();
            model.logtime = dataRow["logtime"].ToString();
            model.nowlogtime = dataRow["nowlogtime"].ToString();
            model.LoginIP = dataRow["LoginIP"].ToString();
            model.fdnum = dataRow["fdnum"].ToString();
            model.level = Convert.ToDecimal(dataRow["level"].ToString());
            model.levels = dataRow["levels"].ToString();
            model.BankPass = dataRow["BankPass"].ToString();
            object ojb = dataRow["loginNum"];
            if ((ojb != null) && (ojb != DBNull.Value))
            {
                model.loginNum = (int)ojb;
            }
            model.state = Convert.ToInt32(dataRow["state"].ToString());
            model.CHS_State = dataRow["CHS_State"].ToString().Trim();
            model.rate_dw = Convert.ToDouble(dataRow["rate_dw"].ToString());
            model.userclass = Convert.ToInt32(dataRow["userclass"].ToString());
            model.CHS_userclass = dataRow["CHS_userclass"].ToString();
            model.online = dataRow["online"].ToString();
            return model;
        }

        public TSL.Model.SYS_users ReaderBind(IDataReader dataReader)
        {
            TSL.Model.SYS_users model = new TSL.Model.SYS_users();
            model.id = dataReader["ID"].ToString();
            model.username = dataReader["username"].ToString();
            model.userpass = dataReader["userpass"].ToString();
            model.CHS_name = dataReader["CHS_name"].ToString();
            model.limit = dataReader["limit"].ToString();
            model.uermoney = Convert.ToDecimal(dataReader["uermoney"]);
            model.lockmoney = Convert.ToDecimal(dataReader["lockmoney"]);
            model.bank_bank = Convert.ToInt32(dataReader["bank_bank"].ToString());
            model.bank_area = dataReader["bank_area"].ToString();
            model.bank_name = dataReader["bank_name"].ToString();
            model.bank_num = dataReader["bank_num"].ToString();
            model.qq = dataReader["qq"].ToString();
            model.addtime = dataReader["addtime"].ToString();
            model.regfrom = dataReader["regfrom"].ToString().Trim();
            model.email = dataReader["email"].ToString();
            model.logtime = dataReader["logtime"].ToString();
            model.nowlogtime = dataReader["nowlogtime"].ToString();
            model.LoginIP = dataReader["LoginIP"].ToString();
            model.fdnum = dataReader["fdnum"].ToString();
            model.level = Convert.ToDecimal(dataReader["level"].ToString());
            model.levels = dataReader["levels"].ToString();
            model.BankPass = dataReader["BankPass"].ToString();
            object ojb = dataReader["loginNum"];
            if ((ojb != null) && (ojb != DBNull.Value))
            {
                model.loginNum = (int) ojb;
            }
            model.state = Convert.ToInt32(dataReader["state"].ToString());
            model.CHS_State = dataReader["CHS_State"].ToString().Trim();
            model.rate_dw = Convert.ToDouble(dataReader["rate_dw"].ToString());
            model.userclass = Convert.ToInt32(dataReader["userclass"].ToString());
            model.CHS_userclass = dataReader["CHS_userclass"].ToString();
            model.online = dataReader["online"].ToString();
            return model;
        }

        public DataSet Total(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sum(uermoney)uermoney from users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public void Update(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_Update");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "userpass", DbType.AnsiString, model.userpass);
            db.AddInParameter(dbCommand, "BankPass", DbType.AnsiString, model.BankPass);
            db.AddInParameter(dbCommand, "limit", DbType.AnsiString, model.limit);
            db.AddInParameter(dbCommand, "rate_dw", DbType.AnsiString, model.rate_dw);
            db.AddInParameter(dbCommand, "CHS_name", DbType.AnsiString, model.CHS_name);
            db.AddInParameter(dbCommand, "qq", DbType.AnsiString, model.qq);
            db.AddInParameter(dbCommand, "bank_bank", DbType.AnsiString, model.bank_bank);
            db.AddInParameter(dbCommand, "bank_num", DbType.AnsiString, model.bank_num);
            db.AddInParameter(dbCommand, "regfrom", DbType.AnsiString, model.regfrom);
            db.AddInParameter(dbCommand, "email", DbType.AnsiString, model.email);
            db.AddInParameter(dbCommand, "level", DbType.AnsiString, model.level);
            db.AddInParameter(dbCommand, "userclass", DbType.Int32, model.userclass);
            db.AddInParameter(dbCommand, "bank_name", DbType.AnsiString, model.bank_name);
            db.AddInParameter(dbCommand, "state", DbType.AnsiString, model.state);
            db.ExecuteNonQuery(dbCommand);
        }



        public void Update_FundPwd(string username, string pwd)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_Update_Fundpwd");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, username);
            db.AddInParameter(dbCommand, "userpass", DbType.AnsiString, pwd);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Update_Pwd(string username, string pwd)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_Update_pwd");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, username);
            db.AddInParameter(dbCommand, "userpass", DbType.AnsiString, pwd);
            db.ExecuteNonQuery(dbCommand);
        }

        public void UpdateLogin(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_users_UpdateLogin");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "loginNum", DbType.Int32, model.loginNum);
            db.AddInParameter(dbCommand, "LogTime", DbType.AnsiString, model.logtime);
            db.AddInParameter(dbCommand, "LoginIP", DbType.AnsiString, model.LoginIP);
            db.ExecuteNonQuery(dbCommand);
        }

        public void UpdatePwd(TSL.Model.SYS_users model)
        {
        }

        public void UpdateUser(TSL.Model.SYS_users model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Update_SetName");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "CHS_name", DbType.AnsiString, model.CHS_name);
            db.AddInParameter(dbCommand, "bank_name", DbType.AnsiString, model.bank_name);
            db.AddInParameter(dbCommand, "qq", DbType.AnsiString, model.qq);
            db.AddInParameter(dbCommand, "bank_bank", DbType.AnsiString, model.bank_bank);
            db.AddInParameter(dbCommand, "bank_num", DbType.AnsiString, model.bank_num);
            db.AddInParameter(dbCommand, "email", DbType.AnsiString, model.email);
            db.AddInParameter(dbCommand, "BankPass", DbType.AnsiString, model.BankPass);
            db.ExecuteNonQuery(dbCommand);
        }
    }
}

